<?php
/**
 * User: xuanbang.wen
 * Date: 2019/12/24
 */

namespace App\Web\Model;


use App\Service\DbIndex;
use App\Web\Facades\UserAuth;
use EchoPHP\Facades\Db;
use App\Web\Model\Project as ProjectModel;


class ProjectReport extends Model {
    /**
     * Notes:项目数量
     * @param int $userId
     * @param string $teamCode
     * @param $params
     * @return int
     */
    public function projectListCount($params, $userId = 0, $teamCode = '') {
        $sql = 'select count(DISTINCT p.projectid) as count from Project as p LEFT join CustomerSub as cs on p.subid = cs.subid left join ProjectOperator as po on po.projectid = p.projectid left join User as u on po.oprid = u.userid where po.status = ? ';
        $var = ['01'];
        $user = UserAuth::getLoginInfo();

        //如果是普通成员,只能看自己的数据
        if ($user['teamrole'] == 1) {
            $sql .= ' and u.userid = ? ';
            $var[] = UserAuth::getLoginId();
        } else { //如果是团队leader，可以看团队下的数据和团队下某人的数据
            $chekSql = 'select userid from User where teamcode like ?';
            $chkVar = [$user['teamcode'] . '%'];
            $teamUserIds = Db::select($chekSql, $chkVar);
            if (empty($teamUserIds)) {
                return 0;
            }
            $teamUserIds = array_column($teamUserIds, 'userid');
            $baseTeamCode = substr($teamCode, 0, strlen($user['teamcode']));
            if ($userId && in_array($userId, $teamUserIds)) {
                $sql .= ' and u.userid = ? ';
                $var [] = $userId;
            } elseif ($teamCode && $user['teamcode'] == $baseTeamCode) {
                $sql .= ' and u.teamcode like ? ';
                $var [] = $teamCode . '%';
            } else {
                return 0;
            }
        }
        //筛选参数
        if (isset($params['projectname']) && $params['projectname']) {
            $sql .= 'and projectname like ? ';
            $var[] = '%' . $params['projectname'] . '%';
        }
        if (isset($params['subid']) && $params['subid']) {
            $sql .= 'and p.subid = ? ';
            $var[] = $params['subid'];
        }
        if (isset($params['subname']) && $params['subname']) {
            $sql .= 'and subname like ? ';
            $var[] = '%' . $params['subname'] . '%';
        }
        if (isset($params['chargername']) && $params['chargername']) {
            $sql .= 'and u.cname like ? ';
            $var[] = '%' . $params['chargername'] . '%';
            if (isset($params['ischarger']) && $params['ischarger'] == 1) {
                $sql .= 'and po.ischarger = ? ';
                $var[] = 1;
            }
        }

        if (isset($params['status']) && $params['status']) {
            $sql .= 'and p.status = ? ';
            $var[] = $params['status'];
        }

        return Db::selectOne($sql, $var)['count'];

    }

    /**
     * Notes:项目列表
     * @param $params
     * @param int $userId
     * @param string $teamCode
     * @param int $rowFrom
     * @param int $rowTo
     * @return array
     * @throws \Exception
     */
    public function projectList($params, $userId = 0, $teamCode = '', $rowFrom = 0, $rowTo = 0) {
        $sql = 'select p.projectid,p.subid,p.crmprojectnum,p.projectname,p.status,cs.subname,max(po.ischarger) as ischarger from Project as p LEFT join CustomerSub as cs on p.subid = cs.subid left join ProjectOperator as po on po.projectid = p.projectid left join User as u on po.oprid = u.userid where po.status = ? ';
        $var = ['01'];
        $user = UserAuth::getLoginInfo();
        //如果是普通成员,只能看自己的数据
        if ($user['teamrole'] == 1) {
            $sql .= ' and u.userid = ? ';
            $var[] = $user['userid'];
        } else { //如果是团队leader，可以看团队下的数据和团队下某人的数据
            $chekSql = 'select userid from User where teamcode like ?';
            $chkVar = [$user['teamcode'] . '%'];
            $teamUserIds = Db::select($chekSql, $chkVar);
            if (empty($teamUserIds)) {
                return [];
            }
            $teamUserIds = array_column($teamUserIds, 'userid');
            $baseTeamCode = substr($teamCode, 0, strlen($user['teamcode']));
            if ($userId && in_array($userId, $teamUserIds)) {
                $sql .= ' and u.userid = ? ';
                $var [] = $userId;
            } elseif ($teamCode && $user['teamcode'] == $baseTeamCode) {
                $sql .= ' and u.teamcode like ? ';
                $var [] = $teamCode . '%';
            } else {
                return [];
            }
        }

        //筛选参数
        if (isset($params['projectname']) && $params['projectname']) {
            $sql .= 'and projectname like ? ';
            $var[] = '%' . $params['projectname'] . '%';
        }
        if (isset($params['subid']) && $params['subid']) {
            $sql .= 'and p.subid = ? ';
            $var[] = $params['subid'];
        }
        if (isset($params['subname']) && $params['subname']) {
            $sql .= 'and subname like ? ';
            $var[] = '%' . $params['subname'] . '%';
        }
        if (isset($params['chargername']) && $params['chargername']) {
            $sql .= 'and u.cname like ? ';
            $var[] = '%' . $params['chargername'] . '%';
            if (isset($params['ischarger']) && $params['ischarger'] == 1) {
                $sql .= 'and po.ischarger = ? ';
                $var[] = 1;
            }
        }

        if (isset($params['status']) && $params['status']) {
            $sql .= 'and p.status = ? ';
            $var[] = $params['status'];
        }
        $sql .= ' group by p.projectid order by p.projectid desc';
        $projectList = Db::select($sql, $var, $rowFrom, $rowTo);
        $reportMap = $this->fullReport($projectList, $userId, $teamCode);
        foreach ($projectList as &$project) {
            $project['report'] = $reportMap[$project['projectid']];
            $project['trans_status'] = (new ProjectModel())->statusMap[$project['status']];
        }
        return $projectList;
    }

    /**
     * Notes:项目完整报表
     * @param $projectList
     * @param int $userId
     * @param string $teamCode
     * @return array
     * @throws \Exception
     */
    public function fullReport($projectList, $userId = 0, $teamCode = '') {
        $projectIds = array_column($projectList, 'projectid');
        $sql = 'select id as chargewayid,projectid,targetnum as chargewaytargetnum,chargeway,bindflow from ChargeWay where projectid in ( ' . mergeArrSql($projectIds) . ' ) and status = ?';
        $chargeWay = Db::select($sql, array_merge($projectIds, ['01']));
        $thisMonday = $this->thisMonday();

        //生成各种map
        $chargeWayIdMap = $caseVacancyMap = $weeklyReportMap = $chargeWayMap = $scheduleMap = $dbIdMap = [];
        $chargeWayIds = array_column($chargeWay, 'chargewayid');
        $sql = 'select caseid,chargewayid from CaseChargeWay where chargewayid  in ( ' . mergeArrSql($chargeWayIds) . ' ) and status = ?';
        $result = Db::select($sql, array_merge($chargeWayIds, ['01']));
        foreach ($result as $item) {
            $chargeWayIdMap [$item['chargewayid']][] = $item['caseid'];
        }
        $sql = 'select caseid,vacancy from SCase where projectid in ( ' . mergeArrSql($projectIds) . ' )';
        $caseInfo = Db::select($sql, $projectIds);
        foreach ($caseInfo as $item) {
            $caseVacancyMap [$item['caseid']] = (int)$item['vacancy'];
        }
        foreach ($projectList as $item) {
            $dbIndex = DbIndex::getDbIndex($item['subid']);
            $dbIdMap [$dbIndex['dbid']][] = $item['projectid'];
        }

        $weeklyReport = $weeklyTarget = $scheduleReport = $weeklyTargetMap = [];
        foreach ($dbIdMap as $dbId => $dbProjectIds) {
            $sql = 'select caseid,oprid,count,targetnum from R_ReportWeek where projectid in ( ' . mergeArrSql($dbProjectIds) . ' ) and date = ?';
            $weeklyReportResult = Db::connect('jyrpo_' . $dbId)->select($sql, array_merge($dbProjectIds, [date('Ymd', strtotime($thisMonday))]));
            $weeklyReport = array_merge($weeklyReport, $weeklyReportResult);
            $sql = 'select caseid,oprid,targetnum from WeekTarget where projectid in ( ' . mergeArrSql($dbProjectIds) . ' ) and mondaydate = ?';
            $weeklyTargetResult = Db::connect('jyrpo_' . $dbId)->select($sql, array_merge($dbProjectIds, [date('Y-m-d', strtotime($thisMonday))]));
            $weeklyTarget = array_merge($weeklyTarget, $weeklyTargetResult);
            $sql = 'select caseid,oprid,flowatstatus,count from R_ScheduleReport where projectid in ( ' . mergeArrSql($dbProjectIds) . ' )';
            $scheduleresult = Db::connect('jyrpo_' . $dbId)->select($sql, $dbProjectIds);
            $scheduleReport = array_merge($scheduleReport, $scheduleresult);
        }
        foreach ($weeklyReport as $item) {
            $weeklyReportMap[$item['caseid']][$item['oprid']] = $item['count'];
        }
        foreach ($scheduleReport as $item) {
            $scheduleMap[$item['caseid']][] = [
                'flowatstatus' => $item['flowatstatus'],
                'count' => $item['count'],
                'oprid' => $item['oprid'],
            ];
        }
        foreach ($weeklyTarget as $item) {
            $weeklyTargetMap[$item['caseid']][$item['oprid']] = $item['targetnum'];
        }

        //普通用户 我对接的使用了此交付方式的职位
        if ($userId) {
            $sql = 'select distinct c.caseid from SCase as c left join CaseOperator as co on c.caseid = co.caseid where co.oprid = ? and co.status = ?';
            $myAllCaseIds = array_column(Db::select($sql, [$userId, '01']), 'caseid');
            $userIds = [$userId];
        } elseif ($teamCode) {
            //团队leader 我团队成员对接的使用了此交付方式的职位
            $sql = 'select distinct c.caseid from SCase as c left join CaseOperator as co on c.caseid = co.caseid left join User as u on co.oprid = u.userid where co.status = ? and u.teamcode like ? and c.projectid in ( ' . mergeArrSql($projectIds) . ' )';
            $myAllCaseIds = array_column(Db::select($sql, array_merge(['01', $teamCode . '%'], $projectIds)), 'caseid');
            $sql = 'select userid from User where teamcode like ?';
            $result = Db::select($sql, [$teamCode . '%']);
            $userIds = array_column($result, 'userid');
        } else {
            $myAllCaseIds = $userIds = [];
        }
        //循环交付方式 拼接数据
        foreach ($chargeWay as $item) {
            $caseIds = $chargeWayIdMap[$item['chargewayid']] ?? [];
            $myCaseIds = array_intersect($myAllCaseIds, $caseIds);
            $item['casenum'] = count($caseIds);
            $item['caseids'] = implode($caseIds, ',');
            $item['mycasenum'] = count($myCaseIds);
            $item['mycaseids'] = implode($myCaseIds, ',');
            $bindFlows = explode(',', $item['bindflow']);
            $completion = $vacancy = $weeklyTarget = $recommend = $allTargetNum = 0;
            foreach ($caseIds as $caseId) {
                $vacancy += $caseVacancyMap[$caseId];
                foreach (($weeklyReportMap[$caseId] ?? []) as $oprId => $count) {
                    $recommend += $count;
                }
                foreach (($weeklyTargetMap[$caseId] ?? []) as $oprId => $targetNum) {
                    $allTargetNum += $targetNum;
                }
                $schedules = $scheduleMap[$caseId] ?? [];
                foreach ($schedules as $schedule) {
                    if (in_array($schedule['flowatstatus'], $bindFlows)) {
                        $completion += $schedule['count'];
                    }
                }
            }

            $item['vacancy'] = $vacancy;
            $item['recommend'] = $recommend;
            $item['targetnum'] = $allTargetNum;
            $item['completion'] = $completion;
            $myVacancy = $myRecommend = $myTargetNum = $myCompletion = 0;
            foreach ($myCaseIds as $caseId) {
                $myVacancy += $caseVacancyMap[$caseId];
                foreach (($weeklyReportMap[$caseId] ?? []) as $oprId => $count) {
                    if (in_array($oprId, $userIds)) {
                        $myRecommend += $count;
                    }
                }
                foreach (($weeklyTargetMap[$caseId] ?? []) as $oprId => $targetNum) {
                    if (in_array($oprId, $userIds)) {
                        $myTargetNum += $targetNum;
                    }
                }
                $schedules = $scheduleMap[$caseId] ?? [];
                foreach ($schedules as $schedule) {
                    if (in_array($schedule['flowatstatus'], $bindFlows) && in_array($schedule['oprid'], $userIds)) {
                        $myCompletion += $schedule['count'];
                    }
                }
            }
            $item['myvacancy'] = $myVacancy;
            $item['myrecommend'] = $myRecommend;
            $item['mytargetnum'] = $myTargetNum;
            $item['mycompletion'] = $myCompletion;
            $chargeWayMap[$item['projectid']][] = $item;
        }
        return $chargeWayMap;
    }

    /**
     * Notes:添加目标  暂时弃用
     * @param $caseId
     * @param $oprIds
     * @param $targetNum
     * @return bool
     * @throws \Exception
     */
    public function addTarget($caseId, $oprIds, $targetNum) {
        $user = UserAuth::getLoginInfo();
        $monday = $this->thisMonday();
        $sql = 'select projectid from SCase where caseid = ? and status = ?';
        $case = Db::selectOne($sql, [$caseId, '03']);
        if (!$case) {
            return false;
        }
        $projectId = $case['projectid'];
        $sql = 'select subid from Project where projectid = ?';
        $subId = Db::selectOne($sql, [$projectId]);
        if (!$subId) {
            return false;
        }
        $subId = $subId['subid'];
        $dbIndex = DbIndex::getDbIndex($subId);
        $oprInfos = $this->userList($oprIds);
        $teamCodeMap = [];
        foreach ($oprInfos as $info) {
            $teamCodeMap[$info['userid']] = $info['teamcode'];
        }
        $sql = 'select caseid,oprid,createdate from WeekTarget where caseid = ? and mondaydate = ? order by id';
        $existTarget = Db::connect('jyrpo_' . $dbIndex['dbid'])->select($sql, [$caseId, $monday]);
        $existTargetOprIds = [];

        if ($existTarget) {
            $targetCreatedate = current(array_column($existTarget, 'createdate'));
            if ($targetCreatedate && $targetCreatedate < date('Y-m-d H:i:s', strtotime('-1day'))) {
                return false;
            }
            foreach ($existTarget as $item) {
                $existTargetOprIds[] = $item['oprid'];
            }
        }
        $sql = 'insert into WeekTarget (oprid,oprteamcode,caseid,projectid,targetnum,mondaydate,createdate,updatedate,creator,updator) values';
        $values = $var = [];
        foreach ($oprIds as $oprId) {
            //前端不会选到已设置目标的数据，只做返回失败
            if (in_array($oprId, $existTargetOprIds)) {
                return false;
            }
            $values[] = '(?,?,?,?,?,?,Now(),Now(),?,?)';
            $tmp = [
                $oprId,
                $teamCodeMap[$oprId],
                $caseId,
                $projectId,
                $targetNum,
                $monday,
                $user['userid'],
                $user['userid'],
            ];
            $var = array_merge($var, $tmp);
        }
        if (!$values) {
            return false;
        }
        $sql .= implode($values, ',');
        return Db::connect('jyrpo_' . $dbIndex['dbid'])->insert($sql, $var);
    }

    /**
     * Notes:更新目标
     * @param $targetInfo
     * @return array
     * @throws \Exception
     */
    public function updateTarget($targetInfo) {
        $user = UserAuth::getLoginInfo();
        $monday = $this->thisMonday();
        $allCaseIds = array_column($targetInfo, 'caseid');
        if (!$allCaseIds) {
            return [
                'status' => false,
                'message' => '更新失败'
            ];
        }
        $sql = 'select projectid,caseid,status from SCase where caseid in ( ' . mergeArrSql($allCaseIds) . ' )';
        $case = Db::select($sql, $allCaseIds);
        if (!$case) {
            return [
                'status' => false,
                'message' => '更新失败'
            ];
        }
        $caseMap = [];
        foreach ($case as $item) {
            $caseMap [$item['caseid']] = $item['status'];
        }
        $projectIds = array_unique(array_column($case, 'projectid'));

        if (count($projectIds) != 1) {
            return [
                'status' => false,
                'message' => '更新失败'
            ];
        }
        $projectId = $projectIds[0];
        $projectEditRight = (new ProjectModel)->chkEditRight($projectId);
        if (!$projectEditRight) {
            return [
                'status' => false,
                'message' => '无修改权限'
            ];
        }
        $sql = 'select subid from Project where projectid = ?';
        $subId = Db::selectOne($sql, [$projectId]);
        if (!$subId) {
            return [
                'status' => false,
                'message' => '更新失败'
            ];
        }
        $subId = $subId['subid'];
        $dbIndex = DbIndex::getDbIndex($subId);
        $oprIds = array_column($targetInfo, 'oprid');
        $oprInfos = $this->userList($oprIds);
        $teamCodeMap = [];
        foreach ($oprInfos as $info) {
            $teamCodeMap[$info['userid']] = $info['teamcode'];
        }
        //距第一条记录产生超过24h则无法在修改
        $sql = 'select caseid,oprid,createdate from WeekTarget where projectid = ? and mondaydate = ? order by id desc';
        $existTarget = Db::connect('jyrpo_' . $dbIndex['dbid'])->selectOne($sql, [$projectId, $monday]);
        if ($existTarget && $existTarget['createdate'] < date('Y-m-d H:i:s', strtotime('-1day'))) {
            return [
                'status' => false,
                'message' => '已超过修改时间'
            ];
        }

        $result = Db::connect('jyrpo_pdo_' . $dbIndex['dbid'])->transaction(function () use ($targetInfo, $user, $monday, $caseMap, $teamCodeMap, $projectId, $dbIndex) {
            $insertValues = $var = $tmp = [];

            $insertSql = 'insert into WeekTarget (oprid,oprteamcode,caseid,projectid,targetnum,mondaydate,createdate,updatedate,creator,updator) values';
            foreach ($targetInfo as $item) {
                //进行中职位
                if ($caseMap[$item['caseid']] != '01') {
                    continue;
                }
                //需更新数据
                if ($item['targetid'] != 0) {
                    $updateSql = 'update WeekTarget set targetnum = ? , updatedate = NOW() , updator = ? where id = ? and mondaydate = ?';
                    Db::connect('jyrpo_pdo_' . $dbIndex['dbid'])->update($updateSql, [$item['targetnum'], $user['userid'], $item['targetid'], $monday]);
                    continue;
                }
                //需添加数据
                $insertValues [] = '(?,?,?,?,?,?,Now(),Now(),?,?)';
                $tmp = [
                    $item['oprid'],
                    $teamCodeMap[$item['oprid']],
                    $item['caseid'],
                    $projectId,
                    $item['targetnum'],
                    $monday,
                    $user['userid'],
                    $user['userid'],
                ];
                $var = array_merge($var, $tmp);
                //防止变量超过1000
                if (count($var) > 800) {
                    $insertSql .= implode($insertValues, ',');
                    Db::connect('jyrpo_pdo_' . $dbIndex['dbid'])->insert($insertSql, $var);
                    $var = $insertValues = [];
                    $insertSql = 'insert into WeekTarget (oprid,oprteamcode,caseid,projectid,targetnum,mondaydate,createdate,updatedate,creator,updator) values';
                }
            }
            if ($var) {
                $insertSql .= implode($insertValues, ',');
                Db::connect('jyrpo_pdo_' . $dbIndex['dbid'])->insert($insertSql, $var);
            }

            return true;
        });

        return [
            'status' => $result,
            'message' => $result ? '更新成功' : '更新失败'
        ];

    }

    /**
     * Notes:展示目标
     * @param $projectId
     * @return array
     * @throws \Exception
     */
    public function showTarget($projectId) {
        $result = (new ProjectModel())->chkGetRight($projectId);
        if (!$result) {
            return [
                'targetlist' => [],
                'editright' => 0,
            ];
        }
        $sql = 'select subid from Project where projectid = ?';
        $subId = Db::selectOne($sql, [$projectId]);
        if (!$subId) {
            return [
                'status' => false,
                'message' => '更新失败'
            ];
        }
        $subId = $subId['subid'];
        $dbIndex = DbIndex::getDbIndex($subId);
        $monday = $this->thisMonday();
        $sql = 'select c.caseid,c.casename,c.status,c.vacancy,co.oprid from SCase as c left join CaseOperator as co on c.caseid = co.caseid  where c.projectid = ? and co.status = ? and c.status = ? order by caseid,oprid';
        $allCase = Db::select($sql, [$projectId, '01', '01']);
        if (!$allCase) {
            return [
                'targetlist' => [],
                'editright' => 0,
            ];;
        }

        //各种map
        $targetMap = $completionMap = $usernameMap = $chargeWayMap = [];
        $editRight = 0;
        $sql = 'select id as targetid,caseid,oprid,targetnum,createdate from WeekTarget where projectid = ? and mondaydate = ? order by targetid desc';
        $target = Db::connect('jyrpo_' . $dbIndex['dbid'])->select($sql, [$projectId, $monday]);
        if ($target) {
            $firstTarget = current($target);
            if ($firstTarget['createdate'] >= date('Y-m-d H:i:s', strtotime('-1day'))) {
                $editRight = 1;
            }
            foreach ($target as $item) {
                $targetMap[$item['caseid']][$item['oprid']]['targetnum'] = $item['targetnum'];
                $targetMap[$item['caseid']][$item['oprid']]['targetid'] = $item['targetid'];
                $targetMap[$item['caseid']][$item['oprid']]['createdate'] = $item['createdate'];
            }
        } else {
            $editRight = 1;
        }
        $caseIds = array_column($allCase, 'caseid');
        $oprIds = array_unique(array_column($allCase, 'oprid'));
        $userInfo = $this->userList($oprIds);
        foreach ($userInfo as $item) {
            $usernameMap[$item['userid']] = $item['cname'];
        }
        $sql = 'select cw.chargeway,cw.bindflow,ccw.caseid,cs.salarytype from ChargeWay as cw left join CaseChargeWay as ccw on cw.id = ccw.chargewayid left join ChargeStandard as cs on cw.id = cs.chargeid where ccw.caseid in ( ' . mergeArrSql($caseIds) . ' ) and cw.status = ? and ccw.status = ?';
        $caseChargeWay = Db::select($sql, array_merge($caseIds, ['01', '01']));

        foreach ($caseChargeWay as $item) {
            $chargeWayMap[$item['caseid']]['chargeway'] = $item['chargeway'];
            $chargeWayMap[$item['caseid']]['salarytype'] = $item['salarytype'];
//            $chargeWayMap[$item['caseid']]['bindflow'] = $item['bindflow'];
        }
        //已交付人数暂时舍弃
//        $sql = 'select * from R_ScheduleReport where projectid = ? ';
//        $schuedule = Db::select($sql, [$projectId]);
//        $completionMap = [];
//        foreach ($schuedule as $item) {
//            $bindFlows = explode(',', $chargeWayMap[$item['caseid']]['bindflow']);
//            if (in_array($item['flowatstatus'], $bindFlows)) {
//                $completionMap['caseid']['oprid'] += $item['count'];
//            }
//        }

        //循环拼接数据
        foreach ($allCase as &$item) {
            $item['targetid'] = $targetMap[$item['caseid']][$item['oprid']]['targetid'] ?? 0;
            $item['targetnum'] = $targetMap[$item['caseid']][$item['oprid']]['targetnum'] ?? 0;
            $item['chargeway'] = $chargeWayMap[$item['caseid']]['chargeway'] ?? 0;
            $item['salarytype'] = $chargeWayMap[$item['caseid']]['salarytype'] ?? 0;
            $item['createdate'] = $targetMap[$item['caseid']][$item['oprid']]['createdate'] ?? '';
//            $item['completion'] = $completionMap[$item['caseid']][$item['oprid']] ?? 0;
            $item['oprname'] = $usernameMap[$item['oprid']] ?? '';
        }
        //非项目负责人不能修改
        $projectEditRight = (new ProjectModel)->chkEditRight($projectId);
        $editRight = $projectEditRight ? $editRight : 0;
        return [
            'targetlist' => $allCase,
            'editright' => $editRight,
        ];
    }

    /**
     * Notes:进度报表
     * @param $projectId
     * @param $chargeWay
     * @return array
     * @throws \Exception
     */
    public function scheduleReport($projectId, $chargeWay) {
        $result = (new ProjectModel())->chkGetRight($projectId);
        if (!$result) {
            return [];
        }
        $sql = 'select c.caseid,c.casename,c.status,c.vacancy,co.oprid from SCase as c left join CaseOperator as co on c.caseid = co.caseid  where c.projectid = ? and co.status = ?  order by caseid ,oprid';
        $allCase = Db::select($sql, [$projectId, '01']);
        if (!$allCase) {
            return [];
        }
        $sql = 'select subid from Project where projectid = ?';
        $subId = Db::selectOne($sql, [$projectId]);
        if (!$subId) {
            return [];
        }
        $subId = $subId['subid'];
        $dbIndex = DbIndex::getDbIndex($subId);
        $caseOprIds = array_column($allCase, 'oprid');
        $allCaseOpr = [];
        foreach ($allCase as $item) {
            $allCaseOpr[$item['caseid']]['case'] = $item;
            $allCaseOpr[$item['caseid']]['oprids'][] = $item['oprid'];

        }
        $sql = 'select * from R_ScheduleReport where projectid = ?';
        $schuedule = Db::connect('jyrpo_' . $dbIndex['dbid'])->select($sql, [$projectId]);
        if (!$schuedule) {
            return [];
        }
        $schueduleOprIds = array_column($schuedule, 'oprid');
        $oprIds = array_unique(array_merge($caseOprIds, $schueduleOprIds));
        $caseIds = array_column($allCase, 'caseid');
        $chargeWayMap = $usernameMap = $schueduleMap = [];

        $sql = 'select cw.chargeway,cw.bindflow,ccw.caseid from ChargeWay as cw left join CaseChargeWay as ccw on cw.id = ccw.chargewayid where ccw.caseid in ( ' . mergeArrSql($caseIds) . ' ) and cw.status = ? and ccw.status = ?';
        $caseChargeWay = Db::select($sql, array_merge($caseIds, ['01', '01']));
        foreach ($caseChargeWay as $item) {
            $chargeWayMap[$item['caseid']]['chargeway'] = $item['chargeway'];
            $chargeWayMap[$item['caseid']]['bindflow'] = $item['bindflow'];
        }

        $userInfo = $this->userList($oprIds);
        foreach ($userInfo as $item) {
            $usernameMap[$item['userid']] = $item['cname'];
        }
        foreach ($schuedule as $item) {
            $bindFlow = explode(',', $chargeWayMap[$item['caseid']]['bindflow'] ?? '');
            if (in_array($item['flowatstatus'], $bindFlow)) {
                $schueduleMap[$item['caseid']][$item['oprid']]['completion'] = ($schueduleMap[$item['caseid']][$item['oprid']]['completion'] ?? 0) + $item['count'];
            }
            $schueduleMap[$item['caseid']][$item['oprid']]['schuedule'][] = [
                'flowatstatus' => $item['flowatstatus'],
                'count' => $item['count'],
            ];
        }

        foreach ($allCaseOpr as $caseId => &$case) {
            //只返回选择的支付方式下的职位,-1为全部
            if ($chargeWay != '-1' && (!isset($chargeWayMap[$caseId]['chargeway']) || $chargeWayMap[$caseId]['chargeway'] != $chargeWay)) {
                unset($allCaseOpr[$caseId]);
                continue;
            }
            $case['caseid'] = $case['case']['caseid'];
            $case['casename'] = $case['case']['casename'];
            $case['status'] = $case['case']['status'];
            $case['vacancy'] = $case['case']['vacancy'];
            $case['chargeway'] = $chargeWayMap[$caseId]['chargeway'] ?? 0;
            //现在非同团队，但在进度表中记录过的数据，也需展示
            $schueduleOpr = array_keys($schueduleMap[$caseId] ?? []);
            $case['oprids'] = array_unique(array_merge($case['oprids'], $schueduleOpr));
            foreach ($case['oprids'] as $oprId) {
                $case['oprlist'][] = [
                    'oprid' => $oprId,
                    'oprname' => $usernameMap[$oprId] ?? '',
                    'completion' => $schueduleMap[$caseId][$oprId]['completion'] ?? 0,
                    'schuedule' => $schueduleMap[$caseId][$oprId]['schuedule'] ?? [],
                ];
            }
            unset($case['oprids']);
            unset($case['case']);
        }
        return array_values($allCaseOpr);
    }

    /**
     * Notes:推荐量周报
     * @param $startDate
     * @param $endDate
     * @param $projectId
     * @param $chargeWay
     * @return array
     * @throws \Exception
     */
    public function weeklyReport($startDate, $endDate, $projectId, $chargeWay) {
        $result = (new ProjectModel())->chkGetRight($projectId);
        if (!$result) {
            return [
                'weeklyreport' => [],
                'userlist' => []
            ];
        }
        $startDate = date('Ymd', strtotime($startDate));
        $endDate = date('Ymd', strtotime($endDate));
        $sql = 'select subid from Project where projectid = ?';
        $subId = Db::selectOne($sql, [$projectId]);
        if (!$subId) {
            return [
                'weeklyreport' => [],
                'userlist' => []
            ];
        }
        $subId = $subId['subid'];
        $dbIndex = DbIndex::getDbIndex($subId);
        $sql = 'select cw.chargeway,cw.bindflow,ccw.caseid,cs.salarytype from ChargeWay as cw left join CaseChargeWay as ccw on cw.id = ccw.chargewayid left join ChargeStandard as cs on cw.id = cs.chargeid where cw.projectid = ? and cw.status = ? and ccw.status = ? and cw.chargeway = ?';
        $caseChargeWay = Db::select($sql, [$projectId, '01', '01', $chargeWay]);
        $caseIds = array_column($caseChargeWay, 'caseid');
        $chargeWayMap = [];
        foreach ($caseChargeWay as $item) {
            $chargeWayMap[$item['caseid']]['chargeway'] = $item['chargeway'];
            $chargeWayMap[$item['caseid']]['bindflow'] = $item['bindflow'];
            $chargeWayMap[$item['caseid']]['salarytype'] = $item['salarytype'];
        }
        if (!$caseIds) {
            return [
                'weeklyreport' => [],
                'userlist' => []
            ];
        }
        $sql = 'select caseid,oprid,count,targetnum,date from R_ReportWeek where date >= ? and date <= ? and projectid = ? and  caseid in ( ' . mergeArrSql($caseIds) . ' ) order by date,caseid,oprid';
        $weeklyReport = Db::connect('jyrpo_' . $dbIndex['dbid'])->select($sql, array_merge([$startDate, $endDate, $projectId], $caseIds));
        if (!$weeklyReport) {
            return [
                'weeklyreport' => [],
                'userlist' => []
            ];
        }
        $caseIds = array_column($weeklyReport, 'caseid');
        $sql = 'select oprid,caseid from CaseOperator where caseid in ( ' . mergeArrSql($caseIds) . ' ) and status = ?';
        $caseOperator = Db::select($sql, array_merge($caseIds, ['01']));
        foreach ($caseOperator as $item) {
            $caseOprMap[$item['caseid']]['oprids'][] = $item['oprid'];
        }
        $oprIds = array_merge(array_column($weeklyReport, 'oprid'), array_column($caseOperator, 'oprid'));
        $userList = $this->userList($oprIds);
        $teamCodeMap = [];
        foreach ($userList as $item) {
            $teamCodeMap[$item['userid']] = $item['teamcode'];
        }
        $sql = 'select caseid,oprid,targetnum,mondaydate as date from WeekTarget where projectid = ?  and mondaydate >= ? and mondaydate <= ?';
        $weeklyTarget = Db::connect('jyrpo_' . $dbIndex['dbid'])->select($sql, [$projectId, $startDate, $endDate]);
        $targetMap = [];
        foreach ($weeklyTarget as $item) {
            $tmpDate = date('Ymd', strtotime($item['date']));
            $targetMap[$tmpDate][$item['caseid']][$item['oprid']] = $item['targetnum'];
        }

        $caseMap = $completionMap = $weeklyReportMap = [];
        $sql = 'select caseid,casename,vacancy from SCase where caseid in ( ' . mergeArrSql($caseIds) . ' )';
        $caseInfo = Db::select($sql, $caseIds);
        foreach ($caseInfo as $item) {
            $caseMap [$item['caseid']]['casename'] = $item['casename'];
            $caseMap [$item['caseid']]['vacancy'] = (int)$item['vacancy'];
        }

        $sql = 'select * from R_ScheduleReport where projectid = ? ';
        $schuedule = Db::connect('jyrpo_' . $dbIndex['dbid'])->select($sql, [$projectId]);
        $completionMap = [];
        foreach ($schuedule as $item) {
            //节点在绑定节点中的统计人数 为 已交付人数
            $bindFlows = explode(',', $chargeWayMap[$item['caseid']]['bindflow'] ?? '');
            if (in_array($item['flowatstatus'], $bindFlows)) {
                $completionMap[$item['caseid']] = ($completionMap[$item['caseid']] ?? 0) + $item['count'];
            }
        }

        $result1 = [];
        foreach ($weeklyReport as $item) {
            //将同一日期数据放进一个组
            $result1[$item['date'] . $item['caseid']] = [
                'date' => date('Ymd', strtotime($item['date'])),
                'caseid' => $item['caseid'],
            ];
            $weeklyReportMap[$item['date']][$item['caseid']][$item['oprid']] = [
                'oprid' => $item['oprid'],
                'teamcode' => $teamCodeMap[$item['oprid']],
                'targetnum' => $targetMap[$item['date']][$item['caseid']][$item['oprid']] ?? 0,
                'recommend' => $item['count'],
            ];
        }

        $result = array_values($result1);

        foreach ($result as $key => &$item) {
            $item['weeknum'] = (int)date('W', strtotime($item['date']));
            $item['vacancy'] = $caseMap [$item['caseid']]['vacancy'] ?? 0;
            $item['casename'] = $caseMap [$item['caseid']]['casename'] ?? '';
            $item['chargeway'] = $chargeWayMap[$item['caseid']]['chargeway'] ?? 0;
            $item['salarytype'] = $chargeWayMap[$item['caseid']]['salarytype'] ?? 0;
            $item['completion'] = $completionMap[$item['caseid']] ?? 0;
            //把周报中没有，但是WeekTarget中有的数据拼进去
            $tmpOprIds = array_unique(array_merge(array_keys($targetMap[$item['date']][$item['caseid']] ?? []), array_keys($weeklyReportMap[$item['date']][$item['caseid']] ?? [])));
            foreach ($tmpOprIds as $oprId) {
                $item['userreport'][] = $weeklyReportMap[$item['date']][$item['caseid']][$oprId] ?? [
                        'oprid' => $oprId,
                        'teamcode' => $teamCodeMap[$oprId],
                        'targetnum' => $targetMap[$item['date']][$item['caseid']][$oprId] ?? 0,
                        'recommend' => 0,
                    ];
            }

//            $item['userreport'] = $weeklyReportMap[$item['date']][$item['caseid']]['userreport'] ?? [];
        }

        return [
            'weeklyreport' => array_values($result),
            'userlist' => $userList
        ];
    }

    /**
     * Notes:推荐量月报
     * @param $startDate
     * @param $endDate
     * @param $projectId
     * @param $chargeWay
     * @return array
     * @throws \Exception
     */
    public function monthlyReport($startDate, $endDate, $projectId, $chargeWay) {
        $result = (new ProjectModel())->chkGetRight($projectId);
        if (!$result) {
            return [
                'monthlyreport' => [],
                'userlist' => []
            ];
        }
        $startDate = date('Ym', strtotime($startDate));
        $endDate = date('Ymd', strtotime($endDate));
        $sql = 'select subid from Project where projectid = ?';
        $subId = Db::selectOne($sql, [$projectId]);
        if (!$subId) {
            return [
                'monthlyreport' => [],
                'userlist' => []
            ];
        }
        $subId = $subId['subid'];
        $dbIndex = DbIndex::getDbIndex($subId);
        $sql = 'select cw.chargeway,cw.bindflow,ccw.caseid,cs.salarytype from ChargeWay as cw left join CaseChargeWay as ccw on cw.id = ccw.chargewayid left join ChargeStandard as cs on cw.id = cs.chargeid where cw.projectid = ? and cw.status = ? and ccw.status = ? and cw.chargeway = ?';
        $caseChargeWay = Db::select($sql, [$projectId, '01', '01', $chargeWay]);
        $caseIds = array_column($caseChargeWay, 'caseid');
        $chargeWayMap = [];
        foreach ($caseChargeWay as $item) {
            $chargeWayMap[$item['caseid']]['chargeway'] = $item['chargeway'];
            $chargeWayMap[$item['caseid']]['bindflow'] = $item['bindflow'];
            $chargeWayMap[$item['caseid']]['salarytype'] = $item['salarytype'];
        }
        if (!$caseIds) {
            return [
                'monthlyreport' => [],
                'userlist' => []
            ];
        }
        $sql = 'select caseid,oprid,count,date from R_ReportMonth where date >= ? and date <= ? and projectid = ?  and caseid in ( ' . mergeArrSql($caseIds) . ' )order by date,caseid,oprid';
        $monthlyReport = Db::connect('jyrpo_' . $dbIndex['dbid'])->select($sql, array_merge([$startDate, $endDate, $projectId], $caseIds));
        if (!$monthlyReport) {
            return [
                'monthlyreport' => [],
                'userlist' => []
            ];
        }

        $caseIds = array_column($monthlyReport, 'caseid');
        $oprIds = array_unique(array_column($monthlyReport, 'oprid'));
        $userList = $this->userList($oprIds);
        $teamCodeMap = [];
        foreach ($userList as $item) {
            $teamCodeMap[$item['userid']] = $item['teamcode'];
        }

        $caseMap = $chargeWayMap = $completionMap = $monthlyReportMap = [];
        $sql = 'select caseid,casename,vacancy from SCase where caseid in ( ' . mergeArrSql($caseIds) . ' )';
        $caseInfo = Db::select($sql, $caseIds);
        foreach ($caseInfo as $item) {
            $caseMap [$item['caseid']]['casename'] = $item['casename'];
            $caseMap [$item['caseid']]['vacancy'] = (int)$item['vacancy'];
        }

        $sql = 'select cw.chargeway,cw.bindflow,ccw.caseid,cs.salarytype from ChargeWay as cw left join CaseChargeWay as ccw on cw.id = ccw.chargewayid left join ChargeStandard as cs on cw.id = cs.chargeid where ccw.caseid in ( ' . mergeArrSql($caseIds) . ' ) and cw.status = ? and ccw.status = ?';
        $caseChargeWay = Db::select($sql, array_merge($caseIds, ['01', '01']));
        foreach ($caseChargeWay as $item) {
            $chargeWayMap[$item['caseid']]['chargeway'] = $item['chargeway'];
            $chargeWayMap[$item['caseid']]['bindflow'] = $item['bindflow'];
            $chargeWayMap[$item['caseid']]['salarytype'] = $item['salarytype'];
        }

        $sql = 'select * from R_ScheduleReport where projectid = ? ';
        $schuedule = Db::connect('jyrpo_' . $dbIndex['dbid'])->select($sql, [$projectId]);
        $completionMap = [];
        foreach ($schuedule as $item) {
            $bindFlows = explode(',', $chargeWayMap[$item['caseid']]['bindflow'] ?? '');
            if (in_array($item['flowatstatus'], $bindFlows)) {
                $completionMap[$item['caseid']] = ($completionMap[$item['caseid']] ?? 0) + $item['count'];
            }
        }

        $result1 = [];
        foreach ($monthlyReport as $item) {
            $result1[$item['date'] . $item['caseid']] = [
                'date' => $item['date'],
                'caseid' => $item['caseid'],
            ];
            $monthlyReportMap[$item['date']][$item['caseid']]['userreport'][] = [
                'oprid' => $item['oprid'],
                'teamcode' => $teamCodeMap[$item['oprid']],
                'recommend' => $item['count'],
            ];
        }

        $result = array_values($result1);

        foreach ($result as $key => &$item) {
            //按支付方式拆分
            if (($chargeWayMap[$item['caseid']]['chargeway'] ?? 0) != $chargeWay) {
                unset($result[$key]);
                continue;
            }
            $item['vacancy'] = $caseMap [$item['caseid']]['vacancy'] ?? 0;
            $item['casename'] = $caseMap [$item['caseid']]['casename'] ?? '';
            $item['chargeway'] = $chargeWayMap[$item['caseid']]['chargeway'] ?? 0;
            $item['salarytype'] = $chargeWayMap[$item['caseid']]['salarytype'] ?? 0;
            $item['completion'] = $completionMap[$item['caseid']] ?? 0;
            $item['userreport'] = $monthlyReportMap[$item['date']][$item['caseid']]['userreport'] ?? [];
        }

        return [
            'monthlyreport' => array_values($result),
            'userlist' => $userList
        ];
    }

    /**
     * Notes:获取本周一
     * @param $date
     * @return false|string
     */
    protected function thisMonday($date = '') {
        if (!$date) {
            $date = time();
        }
        if (date('w', $date) == 1) {
            return date('Y-m-d');
        }
        return date('Y-m-d', strtotime('-1 monday', $date));
    }

    /**
     * Notes: 分批查询用户数据
     * @param $userIds
     * @return array
     */
    protected function userList($userIds) {
        $userIds = array_unique($userIds);
        $count = count($userIds);
        $userList = [];
        for ($i = 0; $i <= $count; $i += 100) {
            $tmpUserIds = array_slice($userIds, $i, 100);
            if (!$tmpUserIds) {
                break;
            }
            $sql = 'select userid,cname,username,teamcode from User where userid in ( ' . mergeArrSql($tmpUserIds) . ' )';
            $tmpUserList = Db::select($sql, $tmpUserIds);
            $userList = array_merge($userList, $tmpUserList);
        }
        return $userList;
    }

    /**
     * Notes:获取项目支付方式下的职位
     * @param $chargeWayId
     * @param $projectId
     * @return array
     */
    public function getCaseByChargeWay($chargeWayId, $projectId){
        $right = (new ProjectModel())->chkGetRight($projectId);
        if (!$right) {
            return [];
        }
        $sql = 'select caseid,casename,vacancy,status from SCase where caseid in (select cc.caseid from CaseChargeWay as cc left join ChargeWay as cw on cw.id = cc.chargewayid where cw.chargeway = ?) and projectid = ?';
        return Db::select($sql, [$chargeWayId, $projectId]);
    }

    /**
     * Notes:项目下交付方式
     * @param $projectId
     * @return array
     */
    public function getChargewayByProject($projectId){
        $sql = 'select id as chargewayid,projectid,targetnum as chargewaytargetnum,chargeway,bindflow from ChargeWay where projectid = ? and status = ?';
        $chargeWay = Db::select($sql, [$projectId, ['01']]);
        $chargeWayIds = array_column($chargeWay, 'chargewayid');
        $chargeWayIdMap = [];
        $sql = 'select caseid,chargewayid from CaseChargeWay where chargewayid  in ( ' . mergeArrSql($chargeWayIds) . ' ) and status = ?';
        $result = Db::select($sql, array_merge($chargeWayIds, ['01']));
        foreach ($result as $item) {
            $chargeWayIdMap [$item['chargewayid']][] = $item['caseid'];
        }
        $result = [];
        foreach ($chargeWay as $item){
            //支付方式下有职位时进行查询
            if (isset($chargeWayIdMap[$item['chargewayid']])){
                $result[] = [
                    'caseids'=>$chargeWayIdMap[$item['chargewayid']],
                    'flowat'=>$item['bindflow'],
                ];
            }
        }
        return $result;
    }
}